In many applications, particularly those involving banking and other financial record-keeping, computer systems are used which process time-ordered input data. The input data in such computer systems is typically processed by an application computer program. The application computer program makes use of a computer database system to recall stored data values (used in processing the input data) and to store newly processed data. Such computer database systems may be designed in many different ways. A survey of such database systems is found in C. J. Date, An Introduction to Database Systems, 4th Edition, 1986.
An example of a system which combines an application program and a database system is one typically found in a bank. The input to such a system may relate to deposits to or withdrawals for a given set of bank accounts. The application system creates the input and output screens used by the bank workers and processes the data in response to the users' input. The application program uses a database system to manage the storage and retrieval of financial and other data.
In a banking system as described, account records in the database system are used to store the current balance for each bank account. The application computer program processes inputs (for example a deposit to a bank account) to obtain updated values which the database system then stores in the account record for that account (for example to reflect the new current balance after the deposit into the account).
In a conventional database system, once input data is processed by the application program and the database system updates the value of the appropriate record, the original value of that record in the database system is overwritten and can no longer be recalled by the database system. Thus at any given point in time, a conventional database system maintains and presents one static "photograph" of the set of records (the "data domain") as of that one point in time.
Recently, temporal databases have been devised which can represent the progress of states within the database over a period of time. An example of current research in temporal database systems is set out in Rishe, Navathe and Tal, eds., Databases: Theory, Design. and Application, IEEE Computer Society Press, Los Alamitos, Calif., U.S.A., 1991, pages 139-150.
In a temporal database system, when the value of a record is changed, the old value is retained and a new value is inserted into the database. A temporal database system can thus also support requests for data "as of" any past point in time.
In both conventional and temporal databases the database system operates in a passive, record-keeping role. A conventional database provides access only to current data. A temporal database permits the past states of the database to be viewed as in a "motion picture" consisting of many "photographs in sequence" but there is no information retained in the database which indicates what operation or process was carried out on a particular stored state of the database to create the next successive state. In other words, there is no information available from the database to determine what user input or application processing took place to update a record in the database at any particular time.
The present invention relates to temporal transaction oriented databases. Temporal transaction oriented databases are relatively new to the art. Prologic Computer Corporation has developed a database system known as by the trade-mark "Probe" which makes extensive use of the concepts relating to temporal transaction oriented databases as set out below. Probe is described in "The Probe Application Developer's Handbook" and the "Probe Product Description" available from Prologic Computer Corporation.
In temporal transaction oriented databases, there are at least two types of data records--transaction records and master records. Each transaction record is retained in the database as a tuple of attribute-values, including at least the following values:
(a) the entry time of the transaction record in the database; PA1 (b) the effective time of the transaction: when the action corresponding to the transaction record occurs in the business to which the database relates; PA1 (c) detailed information about the transaction necessary for the updating of the master record. A master record may be recalculated by using a stored processing rule, as described in detail, below. Each master record has an associated time-sequenced set of transaction records. The time sequence is determined by the effective time value of each record. The database system inserts new transaction records in the time sequenced set of transaction records. PA1 (a) Misread, or omission of one or more, transaction records in the transaction set; PA1 (b) Misread, or omission of one or more, snapshot records; PA1 (c) Incorrect change, deletion, or update of a processing rule due to application programmer error or configuration error; PA1 (d) Faulty execution of a processing rule due to equipment or system software error. PA1 (a) storing a duplicate of the master record; PA1 (b) retrieving a first record data value representing the effective time of the backdated or reversed transaction record; PA1 (c) retrieving a snapshot record having a second record data value which is the most recent effective time prior to the first data value; PA1 (d) copying the retrieved snapshot record into the master record and into the shadow master record; PA1 (e) for each record and rule data value representing effective time later than the second data value, simultaneously PA1 (f) comparing the duplicate of the master record with the shadow master record and in the event of a difference therebetween signalling the presence of an integrity error. PA1 (a) means for storing a duplicate of the master record; PA1 (b) means for retrieving a first data value representing the effective time of the backdated or reversed transaction record; PA1 (c) means for retrieving a snapshot record having a second data value which is the most recent effective time prior to the first data value; PA1 (d) means for copying the retrieved snapshot record into the master record and means for copying the retrieved snapshot record into the shadow master record, PA1 (e) means for simultaneously PA1 (f) means for comparing the duplicate of the master record with the shadow master record and in the event of a difference therebetween signalling the presence of an integrity error.
The effect of each transaction record on its associated master record is determined by the "processing rule" in effect at the effective time for each transaction record. Each processing rule has a range of effective times associated with it. This range defines when the processing rule is "in effect".
In a temporal transaction oriented database, execution of a transaction processing rule is automatically invoked by the database when triggered by the insertion of a new transaction. In the sense that data (the master record and its set of transaction records) is encapsulated with the business or processing rules for that data, a temporal transaction oriented database is related to object oriented databases, which are more fully discussed in Nahouraii and Perry, eds., Object-Oriented Databases, IEEE Computer Society Press, Los Alamitos, Calif., U.S.A., 1991.
Usually the transaction record with the most recent entry time in the time sequenced set of transaction records is also the transaction with the latest effective time. In effect, the latest transaction record to be inserted in the set of transaction records is the transaction record with the latest effective time. In such a case, the master record is updated by the insertion of the latest transaction record triggering the current processing rule.
It is also possible and desirable to support the insertion of new transaction records with effective times earlier than their entry times (delayed entries or backdated entries) and earlier than the effective time of other transaction records in the time sequenced set of transaction records. Conversely, it is possible and desirable that previously inserted transaction records which are later determined to be erroneous may be marked "reversed". In a temporal transaction oriented database, transaction records are typically retained until it is unlikely that there will be a need for backdates or reversals prior to that effective date. Marking a transaction record as "reversed" permits the temporal transaction oriented database system to ignore the record for most purposes but to retain the record for possible future use or reference.
By supporting delayed or backdated transaction records, and reversed transactions, a temporal transaction oriented database eliminates (automates) many "manual adjusting entries" that would otherwise be needed to correct for master record errors caused by delayed or erroneous entries.
A temporal transaction oriented database system must recalculate the current value of the master record if one or more new transaction records are inserted or if one or more existing transaction records are reversed. (The examples given below deal with the backdated insertion or reversal of a single record, the extension of the methods described to cases having more than one transaction will be apparent to one skilled in the art). In the case of a backdated insertion, a new value for the master record may be calculated by starting with the first (oldest) transaction record in the time sequenced set of transaction records and reprocessing that first transaction record and each subsequent transaction record in the set (including the newly inserted transaction record), using the processing rule in effect at the effective time of each transaction record. In the case of a reversed transaction record the same procedure may be followed but the reversed transaction record is not included in the recalculation of the master record.
The time required to reprocess all stored transaction records for a given master record may be long. A method which results in a shorter recalculation time after a backdated insertion or a reversal of a transaction record is implemented by causing the database system to periodically retain copies of the master record: to store "snapshots" of the master record. According to this method, the recalculation of the current master record value is commenced with the youngest snapshot with an effective time prior to the effective time of the transaction record being inserted.
As the above indicates, because the transaction records and associated processing rules are retained in a temporal transaction oriented database there is no need to retain a snapshot for each different prior state of a master record. Each master record may be recalculated and produced by the database system "as of" any point in time in the manner described above, by replaying the stored transaction records, using the related stored processing rules, commencing at the youngest snapshot which predates the point in time to which master record is being recalculated. Recalculation in this manner using stored processing rules can produce different master record states "as of" different points in time due only to the passage of time, without (or with) transactions during that time. Examples of this include the effect of interest in financial applications or shrinkage in inventory applications.